﻿using Dapper;
using Logistics.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DapperExtensions;
using DapperExtensions.Mapper;
using DapperExtensions.Sql;
using System.Reflection;

namespace Logistics_Dao
{
    public class AccountDao
    {
        private SqlGeneratorImpl sqlGenerator;
        public AccountDao()
        {
            var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new MySqlDialect());
            sqlGenerator = new SqlGeneratorImpl(config);
            DapperExtensions.DapperExtensions.Configure(config);
        }

        public wuliu_account_base GetAccount(string account, string password)
        {
            string sql = "select account_id,account, identity, state, nick_name, phone,password from wuliu_account_base " +
                         "where account=@account and password =@password ";
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {

                return conn.Query<wuliu_account_base>(sql, new { account = account, password = password }).SingleOrDefault();
            }

        }

        /// <summary>
        /// 验证手机号是否有
        /// </summary>
        /// <param name="phone"></param>
        /// <returns></returns>
        public int IsExistPhone(string phone)
        {
            string sql = "select count(1) from wuliu_account_base " +
                         "where phone=@phone  ";
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {

                return conn.ExecuteScalar<int>(sql, new { phone });
            }

        }


        public wuliu_account_base GetByAccountId(string accountid)
        {
            string sql = "select account_id,account, identity, state, nick_name, phone,password from wuliu_account_base " +
                         "where account_id=@accountid ";
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {

                return conn.Query<wuliu_account_base>(sql, new { accountid }).SingleOrDefault();
            }

        }

        /// <summary>
        /// 返回手机端帐号
        /// </summary>
        /// <param name="account"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public object GetPhoneAccount(string account, string password)
        {
            string strsql = @"select b.company_no,b.company_name,b.logo,CONCAT(b.city_name,b.address) as address,b.contacts,b.cont_phone,b.information,b.certificate1,b.certificate2,b.certificate3,a.account,a.nick_name,a.phone,a.email from wuliu_account_base as a
                              INNER JOIN wuliu_company_base as b on a.company_no=b.company_no
                              where a.state=1 and a.account=@account and a.password =@password ";
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
               
                DynamicParameters dynamicParameters = new DynamicParameters();
                dynamicParameters.Add("account", account);
                dynamicParameters.Add("password", password);
                return conn.Query(strsql, dynamicParameters).FirstOrDefault();
            }
        }


        public wuliu_account_base GetAccountById(string account)
        {
            string sql = "select * from wuliu_account_base " +
                         "where account=@account  and user_type=1 ";
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {

                return conn.Query<wuliu_account_base>(sql, new { account = account }).SingleOrDefault();
            }

        }
        /// <summary>
        /// 删除IOS凭证
        /// </summary>
        /// <param name="account"></param>
        /// <param name="ios_token"></param>
        /// <returns></returns>
        public bool DelIOSPhoneToken(string account_id, string ios_token)
        {
            string sql = " DELETE from wuliu_account_ios_token  where account_id=@account_id ";

            if (ios_token != null)
            {
                sql += " or ios_token =@ios_token ";
            }
          
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { account_id, ios_token }) > 0;
            }
        }

        public bool AddIOSPhoneToken(wuliu_account_ios_token mod)
        {
            string sql = @"INSERT INTO `wuliu_account_ios_token` (`account_id`, `ios_token`, `create_time`) 
                           VALUES ('" + mod.account_id + "', '" + mod.ios_token + "', '" + mod.create_time + "');";
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql) > 0;
            }
        }
        /// <summary>
        /// 删除安卓凭证
        /// </summary>
        /// <param name="account"></param>
        /// <param name="ios_token"></param>
        /// <returns></returns>
        public bool DelAndroidPhoneToken(string account_id, string android_token)
        {
            string sql = " DELETE from wuliu_account_android_token " +
                         "where account_id=@account_id  ";
            if (android_token!=null)
            {
                sql += " or android_token =@android_token ";
            }
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { account_id, android_token }) > 0;
            }
        }

        public bool AddAndroidPhoneToken(wuliu_account_android_token mod)
        {
            string sql = @"INSERT INTO `wuliu_account_android_token` (
	     `account_id`, `android_token`, `create_time`
        ) VALUES ('" + mod.account_id + "','" + mod.android_token + "','" + mod.create_time + "');";
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql)>0;
            }
        }

        /// <summary>
        /// 验证帐号和密码是否存在
        /// </summary>
        /// <param name="account"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool CheckAccount(string account, string password)
        {
            string sql = "select account_id,account, identity, state, nick_name, phone from wuliu_account_base " +
                         "where account=@account and password =@password ";
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new {account, password }) > 0;
            }

        }

        public void RecordLoginInfo(wuliu_account_system_login loginInfo)
        {
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
                var sql = @"insert into  wuliu_account_system_login (account_id,login_datetime,login_ip)
                        values (@account_id,@login_datetime,@login_ip)";
                //conn.Execute(sql, loginInfo);
            }
        }

        public int GetParentId(int account_id)
        {
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
                var sql = @"select parentId from wuliu_account_base where account_id=@account_id";
                return conn.ExecuteScalar<int>(sql, new  { account_id });
            }
        }

        public wuliu_account_base GetCompanyId(int account_id)
        {
            using (IDbConnection conn = ConnectionHelper.OpenConnection())
            {
                //var sql = "select * from wuliu_account_company where account_id=@account_id";
                return conn.Get<wuliu_account_base>(account_id);
            }
        }

        public List<wuliu_account_base> GetLxList(int company_id)
        {
            IPredicate com = Predicates.Field<wuliu_account_base>(f => f.company_id, Operator.Eq, company_id);
            IPredicate type = Predicates.Field<wuliu_account_base>(f => f.type, Operator.Eq, "10");
            IPredicate usertype = Predicates.Field<wuliu_account_base>(f => f.user_type, Operator.Eq, "1");
            IPredicate pre = Predicates.Group(GroupOperator.And, com, type, usertype);
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_account_base>(p => p.account_id,false)
                                    };
            var pageData = new PageData<wuliu_account_base>();
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.GetList<wuliu_account_base>(pre, sort).ToList();
            }

        }

        public PageData<wuliu_account_base> GetListByCompanyId(int company_id,int pageIndex,int pageSize)
        {
            IPredicate com = Predicates.Field<wuliu_account_base>(f => f.company_id, Operator.Eq, company_id);
            IPredicate type = Predicates.Field<wuliu_account_base>(f => f.type, Operator.Eq, "10");
            IPredicate usertype = Predicates.Field<wuliu_account_base>(f => f.user_type, Operator.Eq, "1");
            IPredicate pre = Predicates.Group(GroupOperator.And, com, type, usertype);
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_account_base>(p => p.account_id,false)
                                    };
            var pageData = new PageData<wuliu_account_base>();
            using (var conn = ConnectionHelper.OpenConnection())
            {
                pageData.Items = conn.GetPage<wuliu_account_base>(pre, sort, pageIndex, pageSize).ToList();
                pageData.TotalNum = conn.Count<wuliu_account_base>(pre);
                pageData.CurrentPage = pageIndex;
                pageData.TotalPageCount =  pageData.TotalNum % pageSize == 0 ?  pageData.TotalNum / pageSize :  pageData.TotalNum / pageSize + 1;
               
            }
            return pageData;
        }

        public bool Add(wuliu_account_base account)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Insert<wuliu_account_base>(account)>0;
            }
        }

        public bool Add(wuliu_account_online account)
        {
            var mod = GetAccountOnline(account.account_id.ToInt32());
            if (mod != null)
            {
                account.account_online_id = mod.account_online_id;
                return UpdateOnline(account)>0;
            }
            else
            {
                using (var conn = ConnectionHelper.OpenConnection())
                {
                    return conn.Insert<wuliu_account_online>(account) > 0;
                }
            }
            
        }
       
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int UpdateOnline(wuliu_account_online model)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                StringBuilder str = new StringBuilder();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update wuliu_account_online set ");
                if (model.account_id.IfNotNull())
                    strSql.Append("account_id=@account_id,");
                if (model.online_key.IfNotNull())
                    strSql.Append("online_key=@online_key,");
                if (model.edatetime.IfNotNull())
                    strSql.Append("edatetime=@edatetime,");
                if (model.sdatetime.IfNotNull())
                    strSql.Append("sdatetime=@sdatetime,");
                if (model.type.IfNotNull())
                    strSql.Append("type=@type,");
                if (model.lengthen.IfNotNull())
                    strSql.Append("lengthen=@lengthen,");
                if (model.online_key_pc.IfNotNull())
                    strSql.Append("online_key_pc=@online_key_pc");
                str.Append(strSql.ToString().TrimEnd(','));
                str.Append(" where account_online_id=@account_online_id ");
                return conn.Execute(str.ToString(), model);
            }
        }
        /// <summary>
        /// 判断帐号是否在线
        /// </summary>
        /// <param name="account_id"></param>
        /// <param name="online_key"></param>
        /// <param name="type">1：手机端用户，2PC端用户</param>
        /// <returns></returns>
        public wuliu_account_online GetAccountOnline(int account_id)
        {
            var sql = "select * from wuliu_account_online where account_id=@account_id ";
      
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Query<wuliu_account_online>(sql, new { account_id}).FirstOrDefault();
            }
        }

        public wuliu_account_online IsCheckUserToken(string token, int usertime)
        {
            var sql = @"select * from wuliu_account_online where (online_key=@token or online_key_pc=@token) and sdatetime >= @usertime and edatetime <= @usertime";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Query<wuliu_account_online>(sql, new { token, usertime }).FirstOrDefault();
            }
        }

        public bool Update(wuliu_account_base model)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                StringBuilder str = new StringBuilder();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update wuliu_account_base set ");
                if (model.account.IfNotNull())
                    strSql.Append("account=@account,");
                if (model.password.IfNotNull())
                    strSql.Append("password=@password,");
                if (model.password_type.IfNotNull())
                    strSql.Append("password_type=@password_type,");
                if (model.password_key.IfNotNull())
                    strSql.Append("password_key=@password_key,");
                if (model.identity.IfNotNull())
                    strSql.Append("identity=@identity,");
                if (model.state.IfNotNull())
                    strSql.Append("state=@state,");
                if (model.nick_name.IfNotNull())
                    strSql.Append("nick_name=@nick_name,");
                if (model.phone.IfNotNull())
                    strSql.Append("phone=@phone,");
                if (model.company_id.IfNotNull())
                    strSql.Append("company_id=@company_id,");
                if (model.company_no.IfNotNull())
                    strSql.Append("company_no=@company_no,");
                if (model.email.IfNotNull())
                    strSql.Append("email=@email,");
                if (model.company_name.IfNotNull())
                    strSql.Append("company_name=@company_name,");
                if (model.type.IfNotNull())
                    strSql.Append("type=@type,");
                if (model.wuliu_id.IfNotNull())
                    strSql.Append("wuliu_id=@wuliu_id,");
                if (model.wuliu_no.IfNotNull())
                    strSql.Append("wuliu_no=@wuliu_no,");
                if (model.wuliu_name.IfNotNull())
                    strSql.Append("wuliu_name=@wuliu_name,");
                if (model.user_type.IfNotNull())
                    strSql.Append("user_type=@user_type,");
                if (model.plate_num.IfNotNull())
                    strSql.Append("plate_num=@plate_num");
                str.Append(strSql.ToString().TrimEnd(','));
                str.Append(" where account_id=@account_id ");
                return conn.Execute(str.ToString(), model)>0;
            }
        }

        public int UpdateState(int id, int state)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                var sql = "update wuliu_account_base set state=@state where account_id=@id";
                return conn.Execute(sql, new { state, id });
            }
        }
    }
}
